﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWSoft.Framework;

namespace YUHANGMLDZ
{
    public class ExportSQL
    {
        public static decimal getybxmdj(object zifubl)
        {
            var zfbl = decimal.Parse(zifubl.ToString());
            return zfbl / 100 == 0 ? 1 : (zfbl == 1 ? 3 : 2);
        }

        private static decimal GetAutoNumber(string seqname)
        {
            var cmdText = string.Format("SELECT {0}.NEXTVAL SEQNUM FROM dual", seqname);
            try
            {
                return (decimal)DBVisitor.ExecuteScalar(cmdText);
            }
            catch (Exception ex)
            {
                throw new Exception("获取序列号异常，错误信息：" + ex.Message);
            }
        }

        public static string YB_ZLDMZD(DataTable table)
        {
            const string selectsql = "SELECT YLBXID, YBZCBH, YBZLDM, ZLBZDM, ZLZWMC,YBZLDJ,ZLBZDJ,YBZFBL, HZSRM2, HZSRM1,YBZLID,ZLQYRQ,ZDJLXH,t.rowid FROM YB_ZLDMZD t";
            var sb = new StringBuilder();
            foreach (DataRow item in table.Rows)
            {
                sb.AppendLine("\t105" + "\t" +
                           "1\t" +
                           item["item_code"].ToString().ToUpper() + "\t" +
                            "\t" +
                           item["item_name"].ToString().Replace("'", "‘").Replace("\n", "") + "\t" +
                           getybxmdj(item["scale"]) + "\t" +
                           item["price"] + "\t" +
                           (decimal.Parse(item["scale"].ToString()) / 100) + "\t" +
                           item["code_wb"] + "\t" +
                           item["code_py"] + "\t" +
                           item["serial_item"] + "\t" +
                           item["effect_date"].ToString().Substring(0, 10).Replace("-", "") + "\t" +
                           GetAutoNumber("seq_yb_zldmzd"));
            }
            return sb.ToString();
        }

        public static string YB_YPDMZD(DataTable table)
        {
            var addsql = "SELECT YBYPID, YLBXID, YBZCBH, YBYPDM, YPBZDM, YPZWMC, YBYPDJ, YBYPGG, YBYPLX, YPCDMC, HZSRM2, HZSRM1,YBZFBL,YBYPDW,YBYPJX,ZDJLXH,t.rowid from YB_YPDMZD t where ylbxid=105";

            var sb = new StringBuilder();
            foreach (DataRow item in table.Rows)
            {
                sb.AppendLine("\t" + item["serial_medi"] + "\t" +
                        105 + "\t" +
                        1 + "\t" +
                        item["medi_code"].ToString().ToUpper() + "\t" +
                        item["medi_code"].ToString().ToUpper() + "\t" +
                        item["medi_name"].ToString().Replace("\n", "") + "\t" +
                        getybxmdj(item["self_scale"]) + "\t" +
                        item["standard"].ToString().Replace("\n", "") + "\t" +
                        item["medi_item_type"] + "\t" +
                        item["factory"].ToString().Replace("&", "©").Replace("\n", "") + "\t" +
                        item["code_wb"].ToString().ToUpper() + "\t" +
                        item["code_py"].ToString().ToUpper() + "\t" +
                       (decimal.Parse(item["self_scale"].ToString()) / 100) + "\t" +
                       item["model_name"].ToString().Replace(" ", "") + "\t" +
                        item["unit"].ToString().Replace("\n", "") + "\t" +
                        GetAutoNumber("seq_yb_ypdmzd"));
            }
            return sb.ToString();
        }

        public static string Export(string p, DataTable table)
        {
            switch (p)
            {
                case "0": return YB_ZLDMZD(table);
                case "1": return YB_YPDMZD(table);
                case "2": return YB_JBDMZD(table);
                default: return "";
            }
        }

        private static string YB_JBDMZD(DataTable table)
        {
            var addsql = "insert into Yb_Jbdmzd (YBJBID,YLBXID, YBJBDM, JBBZBM, JBZWMC, ICD10M, HZSRM1, HZSRM2) \r\n";
            var isql = addsql;
            int rows = 0;
            int count = 0;
            var sb = new StringBuilder();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                var item = table.Rows[i];

                count++;
                isql += "SELECT " + "'" + item["serial_icd"] + "'," +
                               "'-105'" + "," +
                         "'" + item["icd"] + "'," +
                         "'" + item["icd"] + "'," +
                         "'" + item["disease"] + "'," +
                         "'" + item["icd"] + "'," +
                         "'" + item["code_wb"] + "'," +
                         "'" + item["code_py"] + "' FROM DUAL union all \r\n";
                rows++;

                if (rows == 1000 || i == table.Rows.Count - 1)
                {
                    sb.AppendLine(isql.Substring(0, isql.Length - 12) + ";\r\n");
                    isql = addsql;
                    rows = 0;
                }
            }
            return sb.ToString();
        }
    }
}
